package cn.huhst.cas.weisiqian.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

import org.apache.log4j.Logger;

import cn.huhst.cas.weisiqian.dbhelp.DBHelper;
import cn.huhst.cas.weisiqian.pojo.Menu;

public class MenuDao{

	private Logger logger = Logger.getLogger(MenuDao.class);

	public boolean addMenu(Menu menu){
		boolean result = false;
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		String sql = "INSERT INTO Menu(id,pid,type,name,url,permission) value(?,?,?,?,?,?)";
		logger.info("sql:" + sql);
		int count = 0;
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1,menu.getId());
			pstmt.setInt(2,menu.getPid());
			pstmt.setString(3,menu.getType());
			pstmt.setString(4,menu.getName());
			pstmt.setString(5,menu.getUrl());
			pstmt.setInt(6,menu.getPermission());
			count = pstmt.executeUpdate();
			if (count > 0) {
				logger.info("添加menu成功");
				result = true;
			} else {
				logger.error("添加menu失败");
			}
		} catch (SQLException e) {
			logger.error("添加menu失败");
		} finally {
			DBHelper.closeDBHelp(connection, pstmt, null);
			logger.info("关闭数据库连接");
		}
		return result;
	}

	public ArrayList<Menu> queryAll() {
		ArrayList<Menu> allMenus = new ArrayList<Menu>();
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM Menu";
		logger.info("sql:" + sql);
		try {
			pstmt = connection.prepareStatement(sql);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Menu menu = new Menu();
				menu.setId(rs.getInt("id"));
				menu.setPid(rs.getInt("pid"));
				menu.setType(rs.getString("type"));
				menu.setName(rs.getString("name"));
				menu.setUrl(rs.getString("url"));
				menu.setPermission(rs.getInt("permission"));
				logger.debug("menu:" + menu);
				allMenus.add(menu);
			}
		} catch (SQLException e) {
			logger.error("查询所有信息时出错", e);
		} finally{
			DBHelper.closeDBHelp(connection, pstmt, rs);
			logger.info("关闭数据库连接");
		}
		return allMenus;
	}

	public ArrayList<Menu> limit(int index,int pageSize) {
		ArrayList<Menu> allMenus = new ArrayList<Menu>();
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT * FROM Menu LIMIT ?,?";
		logger.info("sql:" + sql);
		try {
			pstmt = connection.prepareStatement(sql);
			pstmt.setInt(1, index);
			pstmt.setInt(2, pageSize);
			rs = pstmt.executeQuery();
			while (rs.next()) {
				Menu menu = new Menu();
				menu.setId(rs.getInt("id"));
				menu.setPid(rs.getInt("pid"));
				menu.setType(rs.getString("type"));
				menu.setName(rs.getString("name"));
				menu.setUrl(rs.getString("url"));
				menu.setPermission(rs.getInt("permission"));
				logger.debug("menu:" + menu);
			}
		} catch (SQLException e) {
			logger.error("分页查询出错", e);
		} finally{
			DBHelper.closeDBHelp(connection, pstmt, rs);
			logger.info("关闭数据库连接");
		}
		return allMenus;
	}

	public int getCount() {
		int count = 0;
		Connection connection = DBHelper.getConnection();
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		String sql = "SELECT COUNT(*) FROM Menu";
		logger.info("sql:" + sql);
		try {
			pstmt = connection.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if (rs.next()) {
				count = rs.getInt(1);
				logger.info("记录数目为：" + count);
			}
		} catch (SQLException e) {
			logger.error("查询记录数目时出错", e);
		} finally{
			DBHelper.closeDBHelp(connection, pstmt, rs);
			logger.info("关闭数据库连接");
		}
		return count;
	}

}